/**
 * @author zqd	差旅小组
 *
 * 上午08:47:36
 */
package com.cbts.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.cbts.dao.ReportDao;
import com.cbts.util.Page;

public class ReportDaoImpl extends HibernateDaoSupport implements ReportDao {

	/* 
	 * @see com.cbts.dao.ReportDao#getReport(int, java.lang.String, String)
	 */
	@SuppressWarnings("unchecked")
	public Page getReport(final int type, final String year, final String deid,
			final int firstResult, final int maxResult) {
		// zqd 上午08:57:08
		final Page p=new Page();
		final String classname=classnameChoice(type,deid);
		final String stype=typeChoice(type);	//查询时的字段是months还是quarters
		
		return (Page)getHibernateTemplate().execute(new HibernateCallback(){
			
			 public Object doInHibernate(Session session)
				throws HibernateException, SQLException {
				 
				 if(deid==null){
					 
					 Query query = session.createQuery("select o from "+ classname+ " o "+"where substring(o.id."+stype+",1,4) = "+"\'"+year+"\'");
						if(firstResult!=-1 && maxResult!=-1) query.setFirstResult(firstResult).setMaxResults(maxResult);
						p.setPageItems(query.list());
						query = session.createQuery("select count(*) from "+ classname+ " o "+"where substring(o.id."+stype+",1,4) = "+"\'"+year+"\'");
	                    p.setTotalPageNum((Long)query.uniqueResult());
	                    
				 }else if(year==null){//查询某部门全部的月/季报
					 
					 Query query = session.createQuery("select o from "+ classname+ " o "+"where o.department.departmentId = "+"\'"+deid+"\'");
						if(firstResult!=-1 && maxResult!=-1) query.setFirstResult(firstResult).setMaxResults(maxResult);
						p.setPageItems(query.list());
						query = session.createQuery("select count(*) from "+ classname+ " o "+"where o.department.departmentId = "+"\'"+deid+"\'");
	                    p.setTotalPageNum((Long)query.uniqueResult());
	                    
				 }else if(deid==""){//查询全部部门的某年的季报/月报
					 
					 Query query = session.createQuery("select o from "+ classname+ " o "+"where substring(o.id."+stype+",1,4) = "+"\'"+year+"\'");
						if(firstResult!=-1 && maxResult!=-1) query.setFirstResult(firstResult).setMaxResults(maxResult);
						p.setPageItems(query.list());
						query = session.createQuery("select count(*) from "+ classname+ " o "+"where substring(o.id."+stype+",1,4) = "+"\'"+year+"\'");
	                    p.setTotalPageNum((Long)query.uniqueResult());
	                    
				}else {
					
					Query query = session.createQuery("select o from "+ classname+ " o "+"where substring(o.id."+stype+",1,4) = "+"\'"+year+"\'"+"and o.department.departmentId = "+"\'"+deid+"\'");
					if(firstResult!=-1 && maxResult!=-1) query.setFirstResult(firstResult).setMaxResults(maxResult);
					p.setPageItems(query.list());
					query = session.createQuery("select count(*) from "+ classname+ " o "+"where substring(o.id."+stype+",1,4) = "+"\'"+year+"\'"+"and o.department.departmentId = "+"\'"+deid+"\'");
                    p.setTotalPageNum((Long)query.uniqueResult());
                    
				}
				return p;
			 }
		});
	}
	
	//查找全部
	@SuppressWarnings("unchecked")
	public Page finadAll(final int type, final String deid,
			final int firstResult, final int maxResult) {
		// zqd 上午08:57:08
		final Page p=new Page();
		final String classname=classnameChoice(type,deid);
		
		return (Page)getHibernateTemplate().execute(new HibernateCallback(){
			
			 public Object doInHibernate(Session session)
				throws HibernateException, SQLException {
				 
				 Query query = session.createQuery("select o from "+ classname+ " o "+"where 1=1");
				 
					if(firstResult!=-1 && maxResult!=-1) query.setFirstResult(firstResult).setMaxResults(maxResult);
					
					p.setPageItems(query.list());
					query = session.createQuery("select count(*) from "+ classname+ " o "+"where 1=1");
                    p.setTotalPageNum((Long)query.uniqueResult());
                    
				return p;
			 }
		});
	}

	/* 
	 * @see com.cbts.dao.ReportDao#getYear(int, java.lang.String)
	 */
	@SuppressWarnings("unchecked")
	public List getYear(final int type, final String deid) {
		// zqd 上午09:24:13
		final List<String> p=new ArrayList<String>();
		final String classname=classnameChoice(type,deid);
		final String stype=typeChoice(type);	//查询时的字段是months还是quarters
		
		return (List)getHibernateTemplate().execute(new HibernateCallback(){
			
			 public Object doInHibernate(Session session)
				throws HibernateException, SQLException {
				 
				 if(deid==null){
					 
					 Query query = session.createQuery("select distinct substring(o.id."+stype+",1,4) from "+ classname+ " o ");
					 p.addAll(query.list());
					 
				 }else if(deid.equals("")){//查询部门报表的全部年
					 
					 Query query = session.createQuery("select distinct substring(o.id."+stype+",1,4) from "+ classname+ " o ");
					 p.addAll(query.list());
					 
				 }else {//查询具体部门id报表的全部年
					 
					 Query query = session.createQuery("select distinct substring(o.id."+stype+",1,4) from "+ classname+ " o where o.department.departmentId = "+"\'"+deid+"\'");
					 p.addAll(query.list());
				}
				return p;
			 }
		});
	}
	
	private String typeChoice(int type){
		return ((type==0) ? "months":"quarters");
	}
	
	private String classnameChoice(int type, String deid){
		
		switch(type){
		case 0:	//月报
			if(deid==null)	//co
				return "CorporationMonthReport";
			else	//de
				return "DepartmentMonthReport";
		case 1:	//季报
			if(deid==null)	//co
				return "CorporationQuarterReport";
			else	//de
				return "DepartmentQuarterReport";
		default:
			return null;
		}
	}

	
}
